package DAOMysql;


import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import DAO.DAOFactory;
import DAO.FacilitiesDAO;
import DAO.NotFoundException;
import DAO.UserDAO;
import model.UserDTO;
import model.UserSearchNameDTO;

public class UserDAOImpl implements UserDAO{

 
	public int insertUser(UserDTO user) throws NotFoundException{
		
		String insertSql = "INSERT into user(UserName,Password,Role,ContactNo,Email,ReportsTo) VALUES ('"+user.getUsername()+"','"+ user.getPassword()+"','"+user.getRole()+"','"+user.getContactNo()+"','"+user.getEmail()+"','"+user.getReportTo()+"');";
		int autoIncKeyFromFunc = -1;
	    
		try {
		Class.forName("com.mysql.jdbc.Driver");
	} catch (ClassNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
		ResultSet rs = null;
		Connection conn=null;
	try {
		conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
		Statement st = conn.createStatement();
		// Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing insert: " + insertSql);
		int res = st.executeUpdate(insertSql, Statement.RETURN_GENERATED_KEYS);
		rs = st.executeQuery("SELECT LAST_INSERT_ID()");
		 if (rs.next()) {
		        autoIncKeyFromFunc = rs.getInt(1);
		    } else {
		        // throw an exception from here
		    }

		    rs.close();
		    System.out.println("Key returned from " +
                    "'SELECT LAST_INSERT_ID()': " +
                    autoIncKeyFromFunc);
		st.close();
	} catch (Exception e) {
		String error = "Error inserting users. Nested Exception is: " + e;
		// Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
		throw new NotFoundException(error);
	} finally {
		try {
			//conn.rollback();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		}
	return autoIncKeyFromFunc;
	}
    public void updateUser(UserDTO user) throws NotFoundException{
		String updateSql = "UPDATE user SET Role = '"
				+ user.getRole() + "', ContactNo = '" + user.getContactNo() + "', Email = '" + user.getEmail()+ "', ReportsTo = '" + user.getReportTo()
				+ "' WHERE UserID='" +user.getUserId() + "' AND UserName='" + user.getUsername()+"';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
			Statement st = conn.createStatement();
			// Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing update: " + updateSql);
			int res = st.executeUpdate(updateSql);
			st.close();
		} catch (Exception e) {
			String error = "Error updating users. Nested Exception is: " + e;
			// Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		} finally {
			try {
//				conn.rollback();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
    	
    }
    public void deleteUser(UserDTO user) throws NotFoundException{
    	String deleteSql = "DELETE FROM user WHERE UserID='"+ user.getUserId() + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		Connection conn = null;
		try {
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
			Statement st = conn.createStatement();
			//Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing update: " + deleteSql);
			st.executeUpdate(deleteSql);
			st.close();
		} catch (Exception e) {
			String error = "Error deleting users. Nested Exception is: " + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		} finally {
			try {
//				conn.rollback();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
    	
    }
    public UserDTO findbyId(int userId) throws NotFoundException{
    	String selectSql = "SELECT * FROM user WHERE UserID='" + userId+ "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		UserDTO oneuser = new UserDTO();
		Connection conn = null;
		try {
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
			Statement st = conn.createStatement();
			//Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				oneuser.setUserId(rs.getInt("UserID"));
				oneuser.setUsername(rs.getString("UserName"));
				oneuser.setPassword(rs.getString("Password"));
				oneuser.setRole(rs.getString("Role"));
				oneuser.setContactNo(rs.getInt("ContactNo"));
				oneuser.setEmail(rs.getString("Email"));
				oneuser.setReportTo(rs.getInt("ReportsTo"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		} finally {
			try {
//				conn.rollback();
				conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}		
		return oneuser;
    }
    
//    public int findAllID() throws NotFoundException{
//    	Connection conn = null;
//    	int id = 0;
//    			try{
//    				Class.forName("com.mysql.jdbc.Driver");
//    			} catch (ClassNotFoundException e1) {
//    				// TODO Auto-generated catch block
//    				e1.printStackTrace();
//    			}
//    			String selectSql = "SELECT UserID FROM user;";
//    			
//    			try {
//    				conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
//    				Statement st = conn.createStatement();
//    				//Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing select: " + selectSql);
//    				ResultSet rs = st.executeQuery(selectSql);
//    				while (rs.next()) {
//    					id = Integer.parseInt(rs.getString("UserID"));
//    				}
//    				st.close();
//    				conn.close();
//    			} catch (Exception ex){
//    				System.out.println(ex);
//    			}
//    			return id;
//    }
   
    public ArrayList<UserDTO> findAllUser() throws NotFoundException{
    	ArrayList<UserDTO> list = new ArrayList<UserDTO>();
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		String selectSql = "SELECT UserID FROM user;";
		Connection conn = null;
		try {
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
			Statement st = conn.createStatement();
			//Logger.getLogger(getClass().getName()).log(Level.INFO,"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				UserDTO oneuser = new UserDTO();
				oneuser.setUserId(rs.getInt("UserID"));
				list.add(oneuser);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			//Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return list;
	}
    
    public UserDTO findUserId(int userId) throws NotFoundException{
		String selectSql = "SELECT * FROM user WHERE UserID='" + userId + "';";
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		UserDTO userdel = new UserDTO();
		Connection conn = null;
		try {
			conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/fbsdatabase","root","password");
			Statement st = conn.createStatement();
//			Logger.getLogger(getClass().getName()).log(Level.INFO,
//					"Executing select: " + selectSql);
			ResultSet rs = st.executeQuery(selectSql);
			while (rs.next()) {
				userdel.setUserId(rs.getInt("UserID"));
				userdel.setUsername(rs.getString("UserName"));
				userdel.setPassword(rs.getString("Password"));
				userdel.setRole(rs.getString("Role"));
				userdel.setContactNo(rs.getInt("ContactNo"));
				userdel.setEmail(rs.getString("Email"));
				userdel.setReportTo(rs.getInt("ReportsTo"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new NotFoundException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		System.out.println(userdel.toString());
		return userdel;
	}
    
    public String findManagerEmail(int userId) throws NotFoundException{
    	int managerID;
    	String managerEmail="";
    	UserDAO u = DAOFactory.loadInstance().getUserDAO();
    	managerID=u.findbyId(userId).getReportTo();
    	managerEmail=u.findbyId(managerID).getEmail().toString();
    	return managerEmail;
    }
    
    public String findUserEmail(int userId) throws NotFoundException{
    	String userEmail="";
    	UserDAO u = DAOFactory.loadInstance().getUserDAO();
    	userEmail=u.findbyId(userId).getEmail().toString();
    	return userEmail;
    }
}
